import pymysql
import re

# 数据库配置（根据实际情况修改）
DB_CONFIG = {
    'host': 'localhost',
    'user': 'root',
    'password': '12345678',
    'database': 'employee_data',
    'charset': 'utf8mb4',
    'cursorclass': pymysql.cursors.DictCursor
}

# 文件路径
FILE_PATH = r'F:/Python/Level One/python_classDesigh/res/employee_test_data.txt'


def validate_data(fields):
    """验证数据有效性"""
    errors = []

    # 验证职工号格式（字母+数字组合）
    if not re.match(r'^[A-Z]{2}\d{3}$', fields[0]):
        errors.append("职工号格式错误（需为2位大写字母+3位数字，如CD001）")

    # 验证性别
    if fields[3] not in ('男', '女'):
        errors.append("性别必须为'男'或'女'")

    # 验证年龄（1-150岁）
    try:
        age = int(fields[2])
        if not 1 <= age <= 150:
            errors.append("年龄超出合理范围")
    except:
        errors.append("年龄必须为数字")

    # 验证工资（正整数）
    try:
        salary = int(fields[5])
        if salary < 0:
            errors.append("工资不能为负数")
    except:
        errors.append("工资必须为数字")

    # 验证电话号码（11位数字）
    phone = fields[7]  # 直接获取原始值
    if phone:  # 只有当phone存在时才验证
        phone_str = str(phone)  # 确保转换为字符串
        if not re.match(r'^\d{11}$', phone_str.strip()):
            errors.append("电话号码必须为11位数字")

    return errors


def process_file():
    """主处理函数"""
    connection = None
    try:
        # 连接数据库
        connection = pymysql.connect(**DB_CONFIG)
        with connection.cursor() as cursor:
            # 统计信息
            total = 0
            success = 0
            errors = 0

            # 打开文件
            with open(FILE_PATH, 'r', encoding='utf-8') as f:
                for line_num, line in enumerate(f, 1):
                    # 跳过空行
                    line = line.strip()
                    if not line:
                        continue

                    # 分割字段（处理连续制表符）
                    raw_fields = re.split(r'\t+', line.replace('\r', '').replace('\n', ''))

                    # 确保字段数量正确（只取前8个字段）
                    processed_fields = raw_fields[:8]
                    if len(processed_fields) < 8:
                        # 不足8个字段时用空字符串补足
                        processed_fields += [''] * (8 - len(processed_fields))

                    # 数据清洗
                    cleaned_data = {
                        'employee_id': processed_fields[0],
                        'name': processed_fields[1],
                        'age': int(processed_fields[2]) if processed_fields[2] else 0,
                        'gender': processed_fields[3],
                        'education': processed_fields[4],
                        'salary': int(processed_fields[5]) if processed_fields[5] else 0,
                        'address': processed_fields[6] or None,
                        'phone': str(processed_fields[7]) if processed_fields[7] else None  #确保转换成字符串
                    }

                    # 数据验证
                    validation_errors = validate_data(list(cleaned_data.values()))
                    if validation_errors:
                        print(f"第{line_num}行数据错误: {', '.join(validation_errors)}")
                        errors += 1
                        continue

                    # 构建SQL语句
                    sql = """INSERT INTO employee 
                            (employee_id, name, age, gender, education, salary, address, phone)
                            VALUES (%s, %s, %s, %s, %s, %s, %s, %s)"""

                    # 执行插入
                    try:
                        cursor.execute(sql, (
                            cleaned_data['employee_id'],
                            cleaned_data['name'],
                            cleaned_data['age'],
                            cleaned_data['gender'],
                            cleaned_data['education'],
                            cleaned_data['salary'],
                            cleaned_data['address'],
                            cleaned_data['phone']
                        ))
                        success += 1
                    except Exception as e:
                        print(f"第{line_num}行插入失败: {str(e)}")
                        errors += 1

                    total += 1

            # 提交事务
            connection.commit()
            print(f"\n处理完成！总记录数：{total}，成功：{success}，失败：{errors}")

    except Exception as e:
        if connection:
            connection.rollback()
        print(f"数据库操作失败: {str(e)}")
    finally:
        if connection:
            connection.close()


if __name__ == '__main__':
    process_file()